﻿using Microsoft.Win32;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Prism.Commands;
using Prism.Events;
using Prism.Regions;
using Prism.Services.Dialogs;
using SmartPlant.Core;
using SmartPlant.Core.Mvvm;
using SmartPlant.Model;
using SmartPlant.Model.DbContext.Biz;
using SmartPlant.Services.Interfaces;
using SmartPlant.WinCore;
using SmartPlant.WinCore.Helper;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.IO;
using System.Windows.Controls;
using System.Windows.Input;
using System.Xml;
//using Telerik.Windows.Diagrams.Core;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
using Telerik.Windows.Documents.Spreadsheet.Model;
using Telerik.Windows.Documents.Spreadsheet.Utilities;
using Unity;

namespace SmartPlant.Modules.Base.ViewModels
{
    public class IndexTableImportViewModel : RegionViewModelBase
    {
        private int templateType = 1;
        private Workbook _workbook = null;
        private DelegateCommand<string> _importFileCommand;
        private DelegateCommand<string> _compareFileCommand;
        private DelegateCommand<object> _templateSelectChangedCommand;
        private DelegateCommand<object> _selectFileCommand;
        private DelegateCommand<object> _refreshTemplatesCommand;

        private string _filePath;
        private readonly ITemplateSettingService _tempServ;
        private readonly IImportUtilityService _importUtilityService;
        private readonly IEventAggregator _eventAggregator;
        private readonly ITaskJobService _taskJobService;
        private bool _enableImport = false;
        private bool _taginsertflag = false;//是否允许导入仪表位号标志
        private bool _insttypeinsertflag = false;//是否允许导入仪表类型
        private bool _replaceInstTypeFlag = false;//是否替换仪表类型描述
        private bool _TestMode = false;//测试模式，True告诉服务端回滚
        private List<ITreeNodeModel> _currentNodes = null;

        private string _busyContent = "数据导入中.....";
        private double _busyProgress = 0D;
        private bool _isBusy;
        private readonly BackgroundWorker backgroundWorker;

        private Report _selectedModel;
        private ObservableCollection<ComboBoxItemModel<Report>> _templateList = null;

        public IndexTableImportViewModel(
            ITemplateSettingService tempServ,
            IImportUtilityService importUtilityService,
            IEventAggregator eventAggregator,
            IDialogService dialogService,
            IRegionManager regionManager,
            IUnityContainer unityContainer,
            ITaskJobService taskJobService)
            : base(regionManager, unityContainer, dialogService)
        {
            _eventAggregator = eventAggregator;
            _tempServ = tempServ;
            _importUtilityService = importUtilityService;
            _templateList = new ObservableCollection<ComboBoxItemModel<Report>>();
            _eventAggregator.GetEvent<PlanSelectedEvent>().Subscribe(OnPlanSelected);
            _eventAggregator.GetEvent<TemplateChangedEvent>().Subscribe(OnTemplateChanged);
            backgroundWorker = new BackgroundWorker();
            backgroundWorker.DoWork += this.OnBackgroundWorkerDoWork;
            backgroundWorker.RunWorkerCompleted += OnBackgroundWorkerRunWorkerCompleted;

            if (GlobalObject.CurrentNodes != null && GlobalObject.CurrentNodes.Count > 0)
            {
                OnPlanSelected(GlobalObject.CurrentNodes);
            }
            _taskJobService = taskJobService;
        }

        private void OnPlanSelected(List<ITreeNodeModel> treeNodes)
        {
            _currentNodes = treeNodes;
        }

        /// <summary>
        /// 允许插入仪表类型
        /// </summary>
        public bool Insttypeinsertflag
        {
            get { return _insttypeinsertflag; }
            set { SetProperty(ref _insttypeinsertflag, value); }
        }

        /// <summary>
        /// 允许替换仪表类型描述
        /// </summary>
        public bool ReplaceInstTypeFlag
        {
            get { return _replaceInstTypeFlag; }
            set { SetProperty(ref _replaceInstTypeFlag, value); }
        }

        /// <summary>
        /// 区分导入还是对比
        /// </summary>
        public enum ImportCompareFlag
        {
            import = 0,
            compare = 1
        }

        /// <summary>
        /// 区分导入还是对比标志位
        /// </summary>
        public ImportCompareFlag importCompareFlag;

        /// <summary>
        /// 是否允许插入仪表位号标志
        /// </summary>
        public bool Taginsertflag
        {
            get { return _taginsertflag; }
            set { SetProperty(ref _taginsertflag, value); }
        }

        /// <summary>
        /// 测试模式，是否允许数据回滚
        /// </summary>
        public bool TestMode
        {
            get { return _TestMode; }
            set { SetProperty(ref _TestMode, value); }
        }
        /// <summary>
        /// 用于显示数据获取的进度文本。
        /// </summary>
        public string BusyContent
        {
            get { return _busyContent; }
            set { SetProperty(ref _busyContent, value); }
        }

        /// <summary>
        /// 用于显示数据获取的进度文本。
        /// </summary>
        public double BusyProgress
        {
            get { return _busyProgress; }
            set { SetProperty(ref _busyProgress, value); }
        }


        /// <summary>
        /// 当前是否处理忙碌状态。
        /// </summary>
        public virtual bool IsBusy
        {
            get { return _isBusy; }
            set
            {
                if (this._isBusy != value)
                {
                    this._isBusy = value;
                    this.OnPropertyChanged(nameof(IsBusy));
                    if (_isBusy)
                    {
                        backgroundWorker.RunWorkerAsync();
                    }
                }
            }
        }

        public bool EnableImport
        {
            get { return _enableImport; }
            set { SetProperty(ref _enableImport, value); }
        }

        public string FilePath
        {
            get { return _filePath; }
            set { SetProperty(ref _filePath, value); }
        }

        /// <summary>
        /// 数据源excel文件，通过telerik控件加载后的对象
        /// </summary>
        public Workbook TemplateWorkbook
        {
            get { return _workbook; }
            set { SetProperty(ref _workbook, value); }
        }

        public Report SelectedModel
        {
            get { return _selectedModel; }
            set { SetProperty(ref _selectedModel, value); }
        }

        public ObservableCollection<ComboBoxItemModel<Report>> ComboxList
        {
            get { return _templateList; }
            set { SetProperty(ref _templateList, value); }
        }

        public ICommand ImportFileCommand
        {
            get
            {
                if (_importFileCommand != null) return _importFileCommand;
                _importFileCommand = new DelegateCommand<string>(onImportCommand);
                return _importFileCommand;
            }
        }

        public ICommand CompareFileCommand
        {
            get
            {
                if (_compareFileCommand != null) return _compareFileCommand;
                _compareFileCommand = new DelegateCommand<string>(onCompareCommand);
                return _compareFileCommand;
            }
        }

        public ICommand TemplateSelectChangedCommand
        {
            get
            {
                if (_templateSelectChangedCommand != null) return _templateSelectChangedCommand;
                _templateSelectChangedCommand = new DelegateCommand<object>(onTemplateSelectChangedCommand);
                return _templateSelectChangedCommand;
            }
        }
        public ICommand refreshTemplatesCommand
        {
            get
            {
                if (_refreshTemplatesCommand != null) return _refreshTemplatesCommand;
                _refreshTemplatesCommand = new DelegateCommand<object>(LoadReports);
                return _refreshTemplatesCommand;
            }
        }
        private async void LoadReports(object obj)
        {
            await OnInitTemplateData((TemplateType)templateType);
        }
        public ICommand SelectFileCommand
        {
            get
            {
                if (_selectFileCommand != null) return _selectFileCommand;
                _selectFileCommand = new DelegateCommand<object>(onSelectFileCommand);
                return _selectFileCommand;
            }
        }



        /// <summary>
        /// 后台任务数据加载完成执行的方法。
        /// </summary>
        /// <param name="e"></param>
        protected virtual void OnBackgroundWorkerRunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            InvokeOnUIThread(() =>
            {
                var result = e.Result as RESTfulResult;
                this.IsBusy = false;
                if (result.succeeded)
                {
                    Dialog.ShowMessage(result.msg);
                }
                else
                {
                    Dialog.ShowAltert(result.msg);
                }

            });
        }

        private void OnBackgroundWorkerDoWork(object sender, DoWorkEventArgs e)
        {
            var task = ImportFileAsync();
            while (!task.IsCompleted)
            {
                Thread.Sleep(1000);
            }
            e.Result = task.Result;
        }
        /// <summary>
        /// 真实的索引表导入
        /// </summary>
        /// <returns></returns>
        private async Task<object> ImportFileAsync()
        {
            var rr = new RESTfulResult { succeeded = false };
            try
            {
                var fi = new FileInfo(_filePath);
                List<NetworkFileInfo> files = new List<NetworkFileInfo>();
                //if (SelectedModel.Name.Contains("xxxx"))
                //{
                //    using var fs = File.OpenRead(_filePath);
                //    byte[] bytes = new byte[fs.Length];
                //    fs.Read(bytes, 0, (int)fs.Length);
                //    files = new List<NetworkFileInfo> { new NetworkFileInfo { FileName = fi.Name, Data = bytes } };
                //}
                //else
                //{
                files = new List<NetworkFileInfo> { new NetworkFileInfo { FileName = fi.Name, Data = _importFileContent } };
                //}
                RESTfulResult<string> result = new() { succeeded = false };
                if (importCompareFlag == ImportCompareFlag.import)
                {
                    result = await _importUtilityService.UploadIndexTableAsync(
                    GlobalObject.CurrentProject.ProjId.ToString(),
                    Path.GetFileNameWithoutExtension(SelectedModel.Name),
                    SelectedModel.TemplatePath.IsNullOrEmpty() ? "null" : SelectedModel.TemplatePath,
                    _currentNodes[0].Id,
                    files, Taginsertflag, Insttypeinsertflag, ReplaceInstTypeFlag, TestMode);//导入
                }
                else
                {
                    result = await _importUtilityService.UploadIndexTableAsync(
                    GlobalObject.CurrentProject.ProjId.ToString(),
                    Path.GetFileNameWithoutExtension(SelectedModel.Name),
                    SelectedModel.TemplatePath.IsNullOrEmpty() ? "null" : SelectedModel.TemplatePath,
                    _currentNodes[0].Id,
                    files);//对比
                }

                if (!result.succeeded)
                {
                    var msg = $"索引表数据导入或对比失败：{result.errors}";
                    LoggerHelper.Current.Error(msg);
                    rr.msg = msg;
                    return rr;
                }
                #region 处理任务
                var taskId = result.data;
                //循环查询job
                while (true)
                {
                    try
                    {
                        TaskJob task = await _taskJobService.GetJobAsync(GlobalObject.CurrentProject.ProjId.ToString(), result.data);
                        if (task != null)
                        {
                            if (task.FINISHED == 1)
                            {
                                break;//结束了，且成功
                            }
                            else if (task.FINISHED == -1)
                            {
                                string msg = $"导入失败：{task.NOTE}";
                                result.msg = msg;
                                return result;
                                //结束了，但是中途错误
                            }
                            BusyProgress = Convert.ToDouble(Math.Round(task.PROGRESS, 0));
                            string progress = task != null ? task.Progress : "任务对象为空";
                            BusyContent = $"当前导入进度：{progress}.....";
                            Thread.Sleep(2000);
                        }
                        else
                        {
                            string msg = $"无法查询到任务{taskId}";
                            rr.msg = msg;
                            return rr;

                        }
                    }
                    catch (Exception x)
                    {
                        string msg = $"导入Excel文件失败：{x.Message}";
                        LoggerHelper.Current.Error(msg, x);
                        rr.msg = msg;
                        return rr;
                    }
                }
                BusyProgress = 0;
                //查询到结束时
                var rFile = await _taskJobService.DownloadFileAsync(GlobalObject.CurrentProject.ProjId.ToString(), taskId);

                if (!rFile.succeeded)
                {
                    rr.msg = rFile.msg;
                    return rr;
                }
                //string形式的文件转为byte数组
                var content = Convert.FromBase64String(rFile.data.Data);

                //using var stream = result.data;
                //var content = await FileHelper.ReadFileContentAsync(stream);

                FilePath = AppDomain.CurrentDomain.BaseDirectory.CombinePath("\\Templates\\" + fi.Name);
                //存到本地磁盘
                FileHelper.WriteFileBinary(FilePath, content);

                #endregion
                try
                {
                    var workBook = OpenXlsxFile(FilePath);
                    TemplateWorkbook = workBook;
                    _importFileContent = null;
                    rr.msg = "索引表数据导入或/对比完成！！！";
                    rr.succeeded = true;
                    return rr;

                }
                catch (Exception x)
                {
                    this.TemplateWorkbook = new Workbook();
                    rr.msg = "打开Excel文件失败，文件可能已加密(损坏)或已被其它进程打开。";
                    rr.succeeded = false;
                    return rr;
                }

            }
            catch (Exception x)
            {
                string msg = $"导入Excel文件失败：{x.Message}";
                LoggerHelper.Current.Error(msg, x);
                rr.msg = msg;
                return rr;
            }
        }
        /// <summary>
        /// Workbook TemplateWorkbook 对应的内容
        /// </summary>
        private byte[] _importFileContent = null;
        private bool _isEmptyTemplateFile = true;


        private void onImportCommand(string obj)
        {
            if (_isEmptyTemplateFile)
            {
                Dialog.ShowAltert("导入前必须选择导入的Excel文件！！！");
                return;
            }
            if (SelectedModel == null)
            {
                Dialog.ShowAltert("导入前必须选择导入的模板类型！！！");
                return;
            }

            if (_currentNodes == null)
            {
                Dialog.ShowAltert("导入前必须选择单元号！！！");
                return;
            }
            if (_currentNodes.Count == 0)
            {
                Dialog.ShowAltert("导入前必须选择单元号！！！");
                return;
            }
            if (_currentNodes.Count > 1)
            {
                Dialog.ShowAltert("索引表导入只能选择一个单元号！！！");
                return;
            }
            IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
            using var output = new MemoryStream();
            formatProvider.Export(TemplateWorkbook, output);
            _importFileContent = output.ToArray();

            //_importFileContent = File.ReadAllBytes(FilePath);
            //FileHelper.WriteFileBinary("C:\\111.xlsx", _importFileContent);
            this.IsBusy = true;
            importCompareFlag = ImportCompareFlag.import;
        }

        private void onCompareCommand(string obj)
        {
            if (_isEmptyTemplateFile)
            {
                Dialog.ShowAltert("导入前必须选择导入的Excel文件！！！");
                return;
            }
            if (SelectedModel == null)
            {
                Dialog.ShowAltert("导入前必须选择导入的模板类型！！！");
                return;
            }

            if (_currentNodes == null)
            {
                Dialog.ShowAltert("导入前必须选择单元号！！！");
                return;
            }
            if (_currentNodes.Count == 0)
            {
                Dialog.ShowAltert("导入前必须选择单元号！！！");
                return;
            }
            if (_currentNodes.Count > 1)
            {
                Dialog.ShowAltert("索引表导入只能选择一个单元号！！！");
                return;
            }
            IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
            using var output = new MemoryStream();
            formatProvider.Export(TemplateWorkbook, output);
            _importFileContent = output.ToArray();

            //_importFileContent = File.ReadAllBytes(FilePath);
            //FileHelper.WriteFileBinary("C:\\111.xlsx", _importFileContent);
            this.IsBusy = true;
            importCompareFlag = ImportCompareFlag.compare;
        }

        private async void OnTemplateChanged(TemplateType tType)
        {
            if (tType == (TemplateType)templateType)
                await OnInitTemplateData(tType);
        }

        protected override async void OnInitPage(object obj)
        {
            await OnInitTemplateData((TemplateType)templateType);
        }

        /// <summary>
        /// 加载模板列表数据。
        /// </summary>
        /// <param name="templateType"></param>
        /// <returns></returns>
        private async Task OnInitTemplateData(TemplateType tType)
        {
            var list = await _tempServ.GetTemplateLisAsync(GlobalObject.CurrentProject.ProjId.ToString());
            list = list.Where(x => x.Type == tType && x.InOrOut == 0).ToList();
            ComboxList.Clear();
            ComboxList.Add(new ComboBoxItemModel<Report>
            {
                Name = "未选择模板",
                Description = "未选择模板",
                IsEnable = true,
                SelectedModel = new Report { Name = "" }
            });
            foreach (var item in list)
            {
                ComboxList.Add(new ComboBoxItemModel<Report>
                {
                    Description = item.Description,
                    IsEnable = true,
                    Name = item.Name,
                    SelectedModel = item
                });
            }
        }

        private void onSelectFileCommand(object obj)
        {
            var openFileDialog = new OpenFileDialog
            {
                Filter = "Excel文件(优先xlsx)|*.xlsx;*.xls"
            };
            if (openFileDialog.ShowDialog() == true)
            {
                FilePath = openFileDialog.FileName;
                try
                {
                    #region 转xlsx
                    if (FilePath.Contains(".xls") && !FilePath.Contains(".xlsx"))
                    {
                        System.Windows.Forms.MessageBox.Show("请优先选择xlsx格式的文件，若选xls，DI-Intools将帮您转为xlsx，但是某些内容会失真，请悉知！", "提示", System.Windows.Forms.MessageBoxButtons.OK);
                        FilePath = ExcelHelper.xls2xlsx(FilePath.Replace(openFileDialog.SafeFileName, ""), openFileDialog.SafeFileName);
                    }

                    #endregion
                    var workBook = OpenXlsxFile(FilePath);
                    DataFilter(workBook, FilePath, initialRow, WorkSheetName, SplitColumn, SplitIdentifier, CellColAdd);
                    this.TemplateWorkbook = workBook;
                    _isEmptyTemplateFile = false;
                }
                catch (Exception x)
                {
                    Dialog.ShowAltert($"打开Excel文件失败，文件可能已加密(损坏)或已被其它进程打开:{x.Message}");
                    FilePath = "";
                    _isEmptyTemplateFile = true;
                    this.TemplateWorkbook = new Workbook();
                }
            }
            else
            {
                FilePath = "";
                _isEmptyTemplateFile = true;
                this.TemplateWorkbook = new Workbook();
            }
        }
        string initialRow;
        string WorkSheetName;
        string SplitColumn;
        string SplitIdentifier;
        string CellColAdd;
        string ImportFlagColumn;

        /// <summary>
        /// 切换模板
        /// </summary>
        /// <param name="obj"></param>
        private async void onTemplateSelectChangedCommand(object obj)
        {
            EnableImport = false;
            _isEmptyTemplateFile = true;
            var eventArgs = obj as SelectionChangedEventArgs;
            if (eventArgs == null) return;
            if (eventArgs.AddedItems.Count < 1) return;

            var selectObj = eventArgs.AddedItems[0] as ComboBoxItemModel<Report>;
            _selectedModel = null;
            if (selectObj != null && selectObj.SelectedModel.Name.IsNotEmptyOrNull())
            {
                _selectedModel = selectObj.SelectedModel;
                EnableImport = true;
                try
                {
                    selectObj.SelectedModel.Name = Path.GetFileNameWithoutExtension(selectObj.SelectedModel.Name) + ".xlsx";
                    var result = await _tempServ.DownloadTemplateFileAsync(GlobalObject.CurrentProject.ProjId.ToString(), selectObj.SelectedModel);

                    if (!result.succeeded)
                    {
                        LoggerHelper.Current.Error(result.errors.ToString());
                        Dialog.ShowAltert(result.errors.ToString());
                        return;
                    }

                    FilePath = AppDomain.CurrentDomain.BaseDirectory.CombinePath("\\Templates\\" + selectObj.SelectedModel.Name);
                    using var stream = result.data;
                    var content = await FileHelper.ReadFileContentAsync(stream);
                    FileHelper.WriteFileBinary(FilePath, content);

                    selectObj.SelectedModel.Name = Path.GetFileNameWithoutExtension(selectObj.SelectedModel.Name) + ".xml";
                    result = await _tempServ.DownloadTemplateFileAsync(GlobalObject.CurrentProject.ProjId.ToString(), selectObj.SelectedModel);
                    if (!result.succeeded)
                    {
                        LoggerHelper.Current.Error(result.errors.ToString());
                        Dialog.ShowAltert(result.errors.ToString());
                        return;
                    }

                    string xmlFilePath = AppDomain.CurrentDomain.BaseDirectory.CombinePath("\\Templates\\" + selectObj.SelectedModel.Name);
                    using var xmlstream = result.data;
                    var xmlcontent = await FileHelper.ReadFileContentAsync(xmlstream);
                    FileHelper.WriteFileBinary(xmlFilePath, xmlcontent);

                    try
                    {
                        XmlDocument doc = new XmlDocument();
                        doc.Load(xmlFilePath);
                        XmlNode IndexImportMapping = doc.DocumentElement.SelectSingleNode("/ImportMapping/IndexImportMapping");
                        if (IndexImportMapping != null)
                        {
                            initialRow = IndexImportMapping.Attributes["initialRow"]?.Value;
                            WorkSheetName = IndexImportMapping.Attributes["WorkSheetName"]?.Value;
                            SplitColumn = IndexImportMapping.Attributes["SplitColumn"]?.Value;
                            SplitIdentifier = IndexImportMapping.Attributes["SplitIdentifier"]?.Value;
                            ImportFlagColumn = IndexImportMapping.Attributes["ImportFlagColumn"]?.Value;
                            var CellColMap = IndexImportMapping.SelectSingleNode("CellColMap[@ColName='CMPNT_NAME']");
                            if (CellColMap != null)
                            {
                                CellColAdd = CellColMap.Attributes["CellColAdd"]?.Value;
                            }
                        }
                        var workBook = OpenXlsxFile(FilePath);
                        DataFilter(workBook, FilePath, initialRow, WorkSheetName, SplitColumn, SplitIdentifier, CellColAdd);
                        this.TemplateWorkbook = workBook;
                        _isEmptyTemplateFile = false;
                        return;
                    }
                    catch (Exception x)
                    {
                        Dialog.ShowAltert($"打开Excel文件失败，文件可能已加密(损坏)或已被其它进程打开:{x.Message}");
                        FilePath = "";
                        _isEmptyTemplateFile = true;
                        this.TemplateWorkbook = new Workbook();
                    }

                }
                catch (Exception x)
                {
                    string msg = $"下载模板文件失败：{x.Message}";
                    LoggerHelper.Current.Error(msg, x);
                    Dialog.ShowAltert(msg);
                }
            }
        }
        private Workbook OpenXlsxFile(string file)
        {
            byte[] fileAsByteArray = File.ReadAllBytes(file);
            return OpenXlsxFile(fileAsByteArray);
        }

        private Workbook OpenXlsxFile(byte[] content)
        {
            IBinaryWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
            Workbook workbook = formatProvider.Import(content);
            return workbook;
        }
        private void DataFilter(Workbook workbook, string FilePath, string initialRow, string WorkSheetName, string SplitColumn, string SplitIdentifier, string CellColAdd)
        {
            if (workbook != null && workbook.Worksheets != null && workbook.Worksheets.Count > 0
                && !string.IsNullOrEmpty(initialRow) && !string.IsNullOrEmpty(WorkSheetName)
                && !string.IsNullOrEmpty(SplitColumn) && !string.IsNullOrEmpty(SplitIdentifier)
                && !string.IsNullOrEmpty(CellColAdd) && !string.IsNullOrEmpty(FilePath))
            {
                //最大列数
                int lastRowNum = 0;
                int lastCellNum = 0;
                using (var fileStream = new FileStream(FilePath, FileMode.Open))
                {
                    IWorkbook iWorkbook;
                    string flieType = Path.GetExtension(FilePath);
                    if (flieType == ".xls")
                    {
                        iWorkbook = new HSSFWorkbook(fileStream);
                    }
                    else
                    {
                        iWorkbook = new XSSFWorkbook(fileStream);
                    }
                    NPOI.SS.UserModel.ISheet iSheet = iWorkbook.GetSheet(WorkSheetName);
                    lastRowNum = iSheet.LastRowNum;
                    for (int i = 0; i < iSheet.LastRowNum; i++)
                    {
                        int cellNum = iSheet.GetRow(i).PhysicalNumberOfCells;
                        if (lastCellNum < cellNum)
                        {
                            lastCellNum = cellNum;
                        }
                    }
                }
                var sheet = workbook.Worksheets[WorkSheetName];

                int rowIndex = NameConverter.ConvertRowNameToIndex(initialRow);
                var tagIndex = NameConverter.ConvertColumnNameToIndex(CellColAdd);
                var quantityIndex = NameConverter.ConvertColumnNameToIndex(SplitColumn);

                for (int i = rowIndex; i < lastRowNum; i++)
                {
                    string tagNo = sheet.Cells[i, tagIndex].GetValue().Value.RawValue;
                    //if (string.IsNullOrEmpty(tagNo))
                    //{
                    //    break;
                    //}
                    if (tagNo.Contains(SplitIdentifier))
                    {
                        var split = tagNo.Split(SplitIdentifier);
                        var length = split.Length;
                        //验证拆分的数量
                        if (length != int.Parse(sheet.Cells[i, quantityIndex].GetValue().Value.RawValue))
                        {
                            continue;
                        }
                        if (length > 1)
                        {
                            var first = split[0];
                            var last = split.Last();
                            var header = first.Substring(0, first.Length - last.Length);
                            split[0] = first.Substring(first.Length - last.Length, last.Length);
                            sheet.Cells[i, tagIndex].SetValue(header + split[0]);
                            sheet.Cells[i, quantityIndex].SetValue(1);
                            sheet.Rows.Insert(i + 1, length - 1);
                            var source = sheet.Cells[i, 0, i, lastCellNum].Copy();
                            for (int j = 1; j < length; j++)
                            {
                                i++;
                                sheet.Cells[i, 0].Paste(source, new PasteOptions(PasteType.All));
                                sheet.Cells[i, tagIndex].SetValue(header + split[j]);
                            }
                        }
                    }
                }
            }
        }
    }
}
